/**
 * <b>项目名称：</b>网络应用框架<br/>
 * <b>包    名：</b>com.hhwy.framework.datasource<br/>
 * <b>文 件 名：</b>FormatSqlFilter.java<br/>
 * <b>版本信息：</b><br/>
 * <b>日    期：</b>2016年11月18日-下午3:58:57<br/>
 * <b>Copyright (c)</b> 2016恒华伟业科技股份有限公司-版权所有<br/>
 * 
 */
package cn.ccccltd.waf.message.filter.sqlfilter;

import java.io.BufferedReader;
import java.io.StringReader;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import com.alibaba.druid.filter.FilterChain;
import com.alibaba.druid.filter.FilterEventAdapter;
import com.alibaba.druid.proxy.jdbc.ConnectionProxy;
import com.alibaba.druid.proxy.jdbc.PreparedStatementProxy;
import com.alibaba.druid.proxy.jdbc.ResultSetProxy;
import com.alibaba.druid.proxy.jdbc.StatementProxy;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.JdbcUtils;

import cn.ccccltd.waf.message.constant.MessageConstants;
import cn.ccccltd.waf.message.context.SpringContext;
import cn.ccccltd.waf.message.init.PropertyContext;
import cn.ccccltd.waf.message.model.params.Order;
import cn.ccccltd.waf.message.model.params.QueryItem;
import cn.ccccltd.waf.message.thread.threadlocal.MessageContext;

 /**
  * 创建日期:2017年11月9日
  * Title:FormatSqlFilter 格式化sql添加过滤
  * Description：对本文件的详细描述，原则上不能少于50字
  * @author yangjingjiang
  * @mender：（文件的修改者，文件创建者之外的人）
  * @version 1.0
  * Remark：认为有必要的其他信息
  */
public class FormatSqlFilter extends FilterEventAdapter{
	
	private static Logger log = LoggerFactory.getLogger(FormatSqlFilter.class);
	
	private static final String FENHAO = ";";
	private static final String COUNT_XING = "count(*)";
	private static final String SELECT = "select";
	
	/**分页自动适配*/
	private DBAdaptation la;
	
	private static String dbType ;
	
	@Override
	public ResultSetProxy statement_executeQuery(FilterChain chain,
			StatementProxy statement, String sql) throws SQLException {
		sql = getSQL(sql);
		return super.statement_executeQuery(chain, statement, sql);
	}
	
    
    @Override
    public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection, String sql)
                                                                                                                        throws SQLException {
    	sql = getSQL(sql);
        return super.connection_prepareStatement(chain, connection, sql);
    }
    
    @Override
    public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,
                                                              String sql, int autoGeneratedKeys) throws SQLException {
    	sql = getSQL(sql);
    	return super.connection_prepareStatement(chain, connection, sql, autoGeneratedKeys);
    }

    @Override
    public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,
                                                              String sql, int resultSetType, int resultSetConcurrency)
                                                                                                                      throws SQLException {
    	sql = getSQL(sql);
    	return super.connection_prepareStatement(chain, connection, sql, resultSetType, resultSetConcurrency);
    }

    @Override
    public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,
                                                              String sql, int resultSetType, int resultSetConcurrency,
                                                              int resultSetHoldability) throws SQLException {
    	sql = getSQL(sql);
        return super.connection_prepareStatement(chain, connection, sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }

    @Override
    public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,
                                                              String sql, int[] columnIndexes) throws SQLException {
    	sql = getSQL(sql);
        return super.connection_prepareStatement(chain, connection, sql, columnIndexes);
    }

    @Override
    public PreparedStatementProxy connection_prepareStatement(FilterChain chain, ConnectionProxy connection,
                                                              String sql, String[] columnNames) throws SQLException {
    	sql = getSQL(sql);
        return super.connection_prepareStatement(chain, connection, sql, columnNames);
    }
    
    /**
     * 功能: 组装sql<br>
     * 作者: yangjingjiang <br>
     * 创建日期:2017年11月15日 <br>
     * 修改者: mender <br>
     * 修改日期: modifydate <br>
     * @param sql
     * @return
     */
    private String getSQL(String sql){
    	
    	log.info(MessageConstants.LOG_DEBUG_PREFIX+"原始sql"+sql);
    	//只过滤select语句
		if(!sql.toLowerCase().startsWith(SELECT)){
			return sql;
		}
    	//init初始化查询
		if(MessageConstants.INIT_DATA){
			return sql;
		}
		
		//使用本地线程不关闭会不会存在内存泄漏  TODO
    	//不过滤单id查询的项
		if(MessageConstants.TRUE.equals(MessageContext.getValue(MessageConstants.NOTFILTERDATA))){
			return sql;
		}
		
		//去除前后的空格
		sql = sql.trim();
		
		if(sql.endsWith(FENHAO)){
			sql = sql.substring(0, sql.length() - 1);
		}
		//自动判断数据库
		if(StringUtils.isBlank(dbType)) {
			dbType = JdbcUtils.getDbType(PropertyContext.getProperty("db.url"), null);
		}
    	sql = getFilterSQL(sql, dbType);
		
		if(la == null) {
			la = SpringContext.getBean(dbType + "Adapter");
		}
		//判断是否查询的是总数如果查询总数则放行
		if(!sql.toLowerCase().contains(COUNT_XING)) {
	    	//添加排序和分页
	    	sql =  la.getLimitSql(sql + getOrderBy());
	    	
		}
    	
		log.info(MessageConstants.LOG_DEBUG_PREFIX + "格式化之后的sql--" + sql);
    	
    	
    	return sql;
    }
    
    /**
     * 功能: 获取权限拼装后的查询语句<br>
     * 作者: yangjingjiang <br>
     * 创建日期:2017年11月15日 <br>
     * 修改者: mender <br>
     * 修改日期: modifydate <br>
     * @param sql
     * @param dbType
     * @return
     */
	private String getFilterSQL(String sql, String dbType) {

		StringBuilder filterSubClause = new StringBuilder();

		//拼接普通的查询条件
		filterSubClause = filterSubClause.append(getQueryItems());
		//是否进行权限过滤
    	if(Boolean.parseBoolean(MessageContext.getValue(MessageConstants.ISFILTERDATA)+"")){
    		log.info(MessageConstants.LOG_DEBUG_PREFIX + "进入权限过滤");
			// 查询系统id
			Object sendSystemid = MessageContext.getValue(MessageConstants.SENDSYSTEMID);
			if (null != sendSystemid && StringUtils.isNotBlank(sendSystemid+"")) {
	
				filterSubClause.append(" AND send_systemid = '" + sendSystemid + "'");
	
			}
			// 创建人id
			Object sendUserid = MessageContext.getValue("sendUserid");
			if (null != sendUserid && StringUtils.isNotEmpty(sendSystemid+"")) {
	
				filterSubClause.append(" AND send_userid = '" + sendUserid + "'");
	
			}
    	}
    	//如果没有数据过滤，则直接返回sql
		if (filterSubClause.toString().trim().length() == 0) {
			return sql;
		}
//		根据数据库进行格式化sql
		SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
		SQLSelectStatement statement = (SQLSelectStatement) parser.parseSelect();
		String formartSQL = statement.getSelect().toString();

		StringReader sr = new StringReader(formartSQL);
		BufferedReader br = new BufferedReader(sr);
		StringBuffer sb = new StringBuffer();
		String line;
		try {
			while ((line = br.readLine()) != null) {
				line = line.trim();
				if (line.startsWith("WHERE")) {
					
					sb.append("WHERE ");
					if (filterSubClause.length() > 0) {
						String trim = filterSubClause.toString().trim();
						sb.append(trim.substring("AND".length())).append(" AND ");
					}
					String tempLine = line.substring("WHERE".length() + 1);
					sb.append(tempLine);
					
				} else {
					sb.append(line).append(" ");
				}
				sb.append(" ");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		String newSql = sb.toString();
		
		newSql = newSql.replaceAll("[ \t]+", " ").trim();
		
		return newSql;
	}
	
	/**
	 * 功能: 获取排序<br>
	 * 作者: yangjingjiang <br>
	 * 创建日期:2017年11月15日 <br>
	 * 修改者: mender <br>
	 * 修改日期: modifydate <br>
	 * @return
	 */
    @SuppressWarnings("unchecked")
	private String getOrderBy() {
    	String orderBySql = "";
		
		List<Order> orders = (List<Order>)MessageContext.getValue(MessageConstants.ORDERS);
		
		StringBuilder sb = new StringBuilder();
		if(!CollectionUtils.isEmpty(orders)) {
			for (Order order : orders) {
				sb.append(order.getSortName()).append(" ");
				sb.append(order.getSortType()).append(",");
				
			}
			orderBySql = orderBySql + " ORDER BY "+sb.toString().substring(0, sb.length()-1);
		}
		return orderBySql;
    }
    
    
    /**
     * 功能: 获取查询项 <br>
     * 作者: yangjingjiang <br>
     * 创建日期:2017年11月15日 <br>
     * 修改者: mender <br>
     * 修改日期: modifydate <br>
     * @return
     */
    @SuppressWarnings("unchecked")
	public String getQueryItems() {
//    	查询条件
		List<QueryItem> queryItems = (List<QueryItem>)MessageContext.getValue(MessageConstants.QUERYITEMS);
//		查询的sql
		String queryItemsSql = "";
//		两个条件之间的关系
		String logic = "";
		if(!CollectionUtils.isEmpty(queryItems)) {
	
			StringBuilder queryBuffer = new StringBuilder();
			
			queryBuffer.append(" AND  (");
			for (int i = 0; i < queryItems.size(); i++) {
				String colwhere = queryItems.get(i).getColwhere();
				logic = queryItems.get(i).getLogic();
				String relation = queryItems.get(i).getRelation();
				String value = queryItems.get(i).getValue();
				
				// sql注入： 单引号转义
				value = value.replace("'", "''");
				
				queryBuffer.append(colwhere);
				//操作符大小写
				Assert.hasText(relation,"relation操作符不能为空！");
				relation = relation.toLowerCase();
				//操作的替换
				if (MessageConstants.RELATION_LIKE.equals(relation) || 
						MessageConstants.RELATION_LLIKE.equals(relation) || 
							MessageConstants.RELATION_RLIKE.equals(relation)) {
					if(MessageConstants.RELATION_LIKE.equals(relation)) {
						value = "%" + value.trim() + "%";
					} else if (MessageConstants.RELATION_RLIKE.equals(relation)) {
						value = "%" + value.trim();
					} else if (MessageConstants.RELATION_LLIKE.equals(relation)) {
						value = value.trim() + "%";
					}
					relation = MessageConstants.RELATION_LIKE;
				} else {
				    if(MessageConstants.RELATION_EQ.equals(relation)) {
				    	relation = " = ";
				    } else if (MessageConstants.RELATION_GT.equals(relation)) {
				    	relation = " > ";
					} else if (MessageConstants.RELATION_GE.equals(relation)) {
						relation = " >= ";
					}else if (MessageConstants.RELATION_LT.equals(relation)) {
						relation = " < ";
					}else if (MessageConstants.RELATION_LE.equals(relation)) {
						relation = " <= ";
					}
				}
				//连接符和值之间添加空格
				queryBuffer.append(" ").append(relation).append(" '" + value + "' ");
				//多个参数连接符添加空格
				queryBuffer.append(logic).append(" ");
			}
			//去除掉连接符合多添加的一个空格
			queryItemsSql = queryBuffer.toString().substring(0,queryBuffer.length()-(logic.length() + 1))+") ";
		}
		log.info(MessageConstants.LOG_DEBUG_PREFIX + "sql"+queryItemsSql);
		return queryItemsSql;
    }
    
}
